﻿Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.IO
Imports System.Windows.Forms.Form
Imports System.Data.Odbc
Imports System.Windows.Forms

Public Class Connection

    ''' <summary>
    ''' Class for Database connection, methods and functions. Supports MSSQL and MySQL. 
    ''' </summary>
    ''' MySQL uses MySQL Connector v1.1
    ''' MSSQL uses SQL connection
    ''' <remarks>
    ''' Author: Dave Villanueva
    ''' Property of Simple Solutions Inc.
    ''' </remarks>

#Region "Variables"
    Enum ServerType
        MSSQL
        MySQL
    End Enum

    Enum ObjectMethod
        Cleardata
        ObjectLock
    End Enum

    Enum eMode
        AddNew
        Edit
        Normal
    End Enum

    Enum eAction
        NewRec
        EditRec
        DeleteRec
        IdleRec
    End Enum

    'Local Variables
    Private _DatabaseServer As String = Nothing
    Private _DatabaseName As String = Nothing
    Private _DatabaseUserName As String = Nothing
    Private _DatabasePassword As String = Nothing
    Private _DatabaseServerType As ServerType = ServerType.MSSQL
    Private _IsConnected As Boolean = False
    Private _DataSet As New DataSet
    Private _sSQL As String

    Private sqlCN_ As New SqlConnection

    'ODBC Variables
    Private sqlCon As New SqlConnection
    Private sqlDA As SqlDataAdapter
    Private sqlReader As SqlDataReader
    Private sqlTran As SqlTransaction

    'MySQL Variables
    Private mysqlCon As New System.Data.Odbc.OdbcConnection
    Private mysqlDA As System.Data.Odbc.OdbcDataAdapter

    'Events
    Public Event Connected()
    Public Event Disconnected()

    'Constant
    Private Const ErrMsg = "Query execution requires an open and available connection to your database server. Please check your provided settings and try again."
    Private Const ErrConnection = "Unable to connect to your database server using the provided settings. Please enter a valid connection settings and try again."
    Public Const TitleHeader = "Simple Solutions ERP"

#End Region

#Region "Properties"
    ''' <summary>
    ''' Gets or set the SqlConnection
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Property SQLCn() As SqlConnection
        Get
            SQLCn = sqlCN_
        End Get
        Set(ByVal value As SqlConnection)
            sqlCN_ = value
        End Set
    End Property

    ''' <summary>
    ''' Gets or sets the hostname or IP address of the database server.
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks>Property of Simple Solutions Inc.</remarks>
    Public Property DatabaseServer() As String
        Get
            Return _DatabaseServer
        End Get
        Set(ByVal value As String)
            _DatabaseServer = value
        End Set
    End Property

    ''' <summary>
    ''' Gets or sets the database name.
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks>Property of Simple Solutions Inc.</remarks>
    Public Property DatabaseName() As String
        Get
            Return _DatabaseName
        End Get
        Set(ByVal value As String)
            _DatabaseName = value
        End Set
    End Property

    ''' <summary>
    ''' Gets or sets the username to use on connecting the database server.
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks>Property of Simple Solutions Inc.</remarks>
    Public Property DatabaseUserName() As String
        Get
            Return _DatabaseUserName
        End Get
        Set(ByVal value As String)
            _DatabaseUserName = value
        End Set
    End Property

    ''' <summary>
    ''' Gets or sets the password for the username entered.
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks>Property of Simple Solutions Inc.</remarks>
    Public Property DatabasePassword() As String
        Get
            Return _DatabasePassword
        End Get
        Set(ByVal value As String)
            _DatabasePassword = value
        End Set
    End Property

    ''' <summary>
    ''' Gets or sets the type of Database Management System. Default is MSSQL.
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks>Property of Simple Solutions Inc.</remarks>
    Public Property DatabaseServerType() As ServerType
        Get
            Return _DatabaseServerType
        End Get
        Set(ByVal value As ServerType)
            _DatabaseServerType = value
        End Set
    End Property

    ''' <summary>
    ''' Returns a boolean value indicating whether the provided connection settings is connected to the database server.
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks>Property of Simple Solutions Inc.</remarks>
    Public ReadOnly Property IsConnected() As Boolean
        Get
            Return _IsConnected
        End Get
    End Property

    ''' <summary>
    ''' Global variable for SQL Script
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Property sSQL() As String
        Get
            sSQL = _sSQL
        End Get
        Set(ByVal value As String)
            _sSQL = value
        End Set
    End Property

#End Region

#Region "Constructor"

    ''' <summary>
    ''' Create a new instance of database connection class.
    ''' </summary>
    ''' <remarks>Property of Simple Solutions Inc.</remarks>
    Sub New()
        'default constructor
    End Sub

    ''' <summary>
    ''' Create a new instance of database connection class providing the connection settings.
    ''' </summary>
    ''' <param name="Host">The hostname or IP address of the database server.</param>
    ''' <param name="Database">The database name.</param>
    ''' <param name="UserName">The username to use to connect to the database server.</param>
    ''' <param name="Password">The password for the username entered.</param>
    ''' <param name="ServerType">The type of Database Management System.</param>
    ''' <remarks>Property of Simple Solutions Inc.</remarks>
    Sub New(ByVal Host As String, ByVal Database As String, ByVal UserName As String, ByVal Password As String, ByVal ServerType As ServerType)
        _DatabaseServer = Host
        _DatabaseName = Database
        _DatabaseUserName = UserName
        _DatabasePassword = Password
        _DatabaseServerType = ServerType

    End Sub

#End Region

#Region "Procedures"

    ''' <summary>
    ''' Bind objects to the current DataTable
    ''' </summary>
    ''' <param name="oCollection">Collection preferably the form where the object is place</param>
    ''' <param name="dTable">Name of the DataTable</param>
    ''' <remarks>Property of Simple Solutions Inc.</remarks>
    Public Sub Databind(ByVal oCollection As Object, ByVal dTable As Object) 'ByVal dTable As DataTable)
        Dim obj As Object
        For Each obj In oCollection.Controls
            If Mid$(obj.tag, 1, 1) = "!" Then
                If TypeName(obj) = "TextBoxX" Then
                    obj.DataBindings.Add("text", dTable, obj.name)
                ElseIf TypeName(obj) = "DateTimeInput" Then
                    obj.DataBindings.Add("text", dTable, obj.name)
                ElseIf TypeName(obj) = "CheckBoxX" Then
                    obj.DataBindings.Add("Checked", dTable, obj.name)
                ElseIf TypeName(obj) = "DataGridViewX" Then
                    obj.datasource = dTable
                    obj.SelectionMode = DataGridViewSelectionMode.FullRowSelect
                    obj.AllowUserToAddRows = False
                    obj.EditMode = DataGridViewEditMode.EditProgrammatically
                    obj.MultiSelect = False
                ElseIf IsContainer(obj) Then
                    Databind(obj, dTable)
                End If
            End If
        Next
    End Sub

    ''' <summary>
    ''' Unbind objects to the current DataTable
    ''' </summary>
    ''' <param name="oCollection">Collection preferably the form where the object is place</param>
    ''' <remarks>Property of Simple Solutions Inc.</remarks>
    Public Sub DataUnbind(ByVal oCollection As Object)
        Dim obj As Object
        For Each obj In oCollection.Controls
            If Mid$(obj.tag, 1, 1) = "!" Then
                If TypeName(obj) = "TextBoxX" Then
                    obj.DataBindings.Clear()
                ElseIf TypeName(obj) = "DateTimeInput" Then
                    obj.DataBindings.Clear()
                ElseIf TypeName(obj) = "CheckBoxX" Then
                    obj.DataBindings.Clear()
                ElseIf IsContainer(obj) Then
                    DataUnbind(obj)
                End If
            End If
        Next
    End Sub

    ''' <summary>
    ''' Controls the objects in the collection
    ''' </summary>
    ''' <param name="oCollection">Collection preferably the form where the object is place</param>
    ''' <param name="oMethod">Either Clear the data or Lock/Unlock the objects</param>
    ''' <param name="lLock">Option when the method is Lock/Unlock</param>
    ''' <remarks></remarks>
    Public Sub ObjectControl(ByVal oCollection As Object, ByVal oMethod As ObjectMethod, Optional ByVal lLock As Boolean = False)
        Dim obj As Object
        If oMethod = ObjectMethod.Cleardata Then
            For Each obj In oCollection.Controls
                If TypeName(obj) = "TextBoxX" Then
                    obj.Text = ""
                ElseIf TypeName(obj) = "DateTimeInput" Then
                    obj.Text = ""
                ElseIf TypeName(obj) = "CheckBoxX" Then
                    obj.checked = False
                ElseIf TypeName(obj) = "ComboBoxEX" Then
                    obj.Items.Clear()
                ElseIf IsContainer(obj) Then
                    ObjectControl(obj, oMethod, lLock)
                End If
            Next

        ElseIf oMethod = ObjectMethod.ObjectLock Then
            For Each obj In oCollection.Controls
                If TypeName(obj) = "TextBoxX" Then
                    obj.Enabled = Not lLock
                ElseIf TypeName(obj) = "DateTimeInput" Then
                    obj.Enabled = Not lLock
                ElseIf TypeName(obj) = "CheckBoxX" Then
                    obj.Enabled = Not lLock
                ElseIf TypeName(obj) = "ComboBoxEX" Then
                    obj.Enabled = Not lLock
                ElseIf IsContainer(obj) Then
                    ObjectControl(obj, oMethod, lLock)
                End If
            Next

        End If
    End Sub

    ''' <summary>
    ''' Populate ComboBox with the result of the sql query
    ''' </summary>
    ''' <param name="obj"></param>
    ''' <param name="FieldName">Returning field of the table </param>
    ''' <param name="TableName">Name of the table</param>
    ''' <param name="Condition">Condition of the query</param>
    ''' <remarks></remarks>
    Public Sub LoadComboValues(ByVal obj As Object, ByVal FieldName As String, ByVal TableName As String, Optional ByVal Condition As String = "")
        Dim sqlDS As DataSet = New DataSet
        Dim sqlDataAdapter As New SqlDataAdapter
        Try
            sSQL = "select " & FieldName & " from " & TableName & IIf(Condition = "", "", " where " & Condition)
            sqlDataAdapter.SelectCommand = New SqlCommand(sSQL, SQLCn)
            sqlDataAdapter.Fill(sqlDS, TableName)
            obj.Text = ""
            obj.Items.Clear()
            For i As Integer = 0 To sqlDS.Tables(TableName).Rows.Count - 1
                obj.items.add(sqlDS.Tables(TableName).Rows(i).Item(FieldName))
            Next
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    ''' <summary>
    ''' Connect to the database server using the provided settings.
    ''' </summary>
    ''' <remarks>Property of Simple Solutions Inc.</remarks>
    Public Sub Connect(ByRef conn As SqlConnection)
        Dim constr As String
        constr = "Data Source=" & DatabaseServer & ";Database=" & _
                DatabaseName & ";UID=" & DatabaseUserName & ";PWD=" & DatabasePassword
        conn = New SqlConnection(constr)
        Try
            If conn.State = ConnectionState.Closed Then
                conn.Open()
            End If
        Catch e As SqlException
            MsgBox("Error: " & e.ToString)
            conn.Close()
        End Try
    End Sub

    ''' <summary>
    ''' Open a record to view
    ''' </summary>
    ''' <param name="sField"></param>
    ''' <param name="sTable"></param>
    ''' <param name="sqlDataSet"></param>
    ''' <param name="conn"></param>
    ''' <remarks></remarks>
    Public Sub OpenRecord(ByVal sField As String, ByVal sTable As String, _
             ByRef sqlDataSet As DataSet, ByVal conn As SqlConnection, _
             Optional ByVal sCondition As String = "")
        'Dim sqlDataSet As New DataSet
        Dim sqlDataAdapter As New SqlDataAdapter
        sSQL = "select " & sField & " from " & sTable & "" & IIf(sCondition = "", "", " where " & sCondition)
        sqlDataAdapter.SelectCommand = New SqlCommand(sSQL, conn)
        sqlDataAdapter.Fill(sqlDataSet, sTable)
        'sqlDataTable = sqlDataSet.Tables(sTable)
    End Sub

    ''' <summary>
    ''' Use to transaction to stored procedure
    ''' </summary>
    ''' <param name="conn"></param>
    ''' <param name="StoredProcedure"></param>
    ''' <param name="oCollection"></param>
    ''' <remarks></remarks>
    Public Sub ExecuteStoredProc(ByVal conn As SqlConnection, ByVal StoredProcedure As String, _
        ByVal oCollection As Object, ByVal oAction As eAction)
        Try
            Dim myCommand As SqlCommand = New SqlCommand(StoredProcedure, conn)
            myCommand.CommandType = CommandType.StoredProcedure
            FillParameters(oCollection, myCommand)
            myCommand.Parameters.Add("@lAction", SqlDbType.Int).Value = oAction
            myCommand.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    ''' <summary>
    ''' Use to fill up parameters to transact stored procedure
    ''' </summary>
    ''' <param name="oCollection"></param>
    ''' <param name="sqlCmd"></param>
    ''' <remarks></remarks>
    Private Sub FillParameters(ByVal oCollection As Object, ByVal sqlCmd As SqlCommand)
        Dim obj As Object
        For Each obj In oCollection.Controls
            If Mid$(obj.tag, 2, 1) = "!" Then
                If TypeName(obj) = "TextBoxX" Then
                    sqlCmd.Parameters.Add("@" & obj.name, SqlDbType.VarChar).Value = obj.text
                ElseIf TypeName(obj) = "DateTimeInput" Then
                    sqlCmd.Parameters.Add("@" & obj.name, SqlDbType.Date).Value = obj.text
                ElseIf TypeName(obj) = "CheckBoxX" Then
                    sqlCmd.Parameters.Add("@" & obj.name, SqlDbType.Bit).Value = obj.checked
                ElseIf TypeName(obj) = "ComboBoxEX" Then
                    sqlCmd.Parameters.Add("@" & obj.name, SqlDbType.VarChar).Value = obj.text
                ElseIf IsContainer(obj) Then
                    FillParameters(obj, sqlCmd)
                End If
            End If
        Next
    End Sub




#End Region

#Region "Functions"

    ''' <summary>
    ''' Execute SQL Statement and return the specified field. Preferably used on getting one record of a certain table.
    ''' </summary>
    ''' <param name="sField">Name of the returning field</param>
    ''' <param name="sTable">Name of the table</param>
    ''' <param name="sCondition">Condition to be applied</param>
    ''' <returns>Return a string</returns>
    ''' <remarks>Property of Simple Solutions Inc.</remarks>
    Public Function GetValueFrTable(ByVal sField As String, ByVal sTable As String, ByVal sCondition As String) As String
        Dim sCond As String
        Dim sqlDS As DataSet = New DataSet
        Dim sqlDataAdapter As New SqlDataAdapter

        If (sCondition <> "") Then
            sCond = " WHERE " & sCondition
        Else
            sCond = ""
        End If

        sSQL = "SELECT " & sField & " FROM " & sTable & sCond
        sqlDataAdapter.SelectCommand = New SqlCommand(sSQL, SQLCn)
        sqlDataAdapter.Fill(sqlDS, sTable)
        GetValueFrTable = sqlDS.Tables(sTable).Rows(0).Item(sField)
    End Function

    ''' <summary>
    ''' Use to determine if the object which was mandatory was filled with data
    ''' </summary>
    ''' <param name="oCollection">Collection preferably the form where the object is place</param>
    ''' <returns>True if ok</returns>
    ''' <remarks></remarks>
    Public Function MandatoryOK(ByVal oCollection As Object) As Boolean
        Dim obj As Object
        Try
            MandatoryOK = True
            For Each obj In oCollection.Controls
                If Mid$(obj.tag, 3, 1) = "!" Then
                    If TypeName(obj) = "TextBoxX" Then
                        If obj.text = "" Then
                            Return False
                        End If
                    ElseIf TypeName(obj) = "DateTimeInput" Then
                        If obj.value = "" Then
                            Return False
                        End If
                    ElseIf TypeName(obj) = "ComboBoxEX" Then
                        If obj.text = "" Then
                            Return False
                        End If
                    ElseIf IsContainer(obj) Then
                        MandatoryOK(obj)
                    End If
                End If
            Next
        Catch ex As Exception
            Return False
        End Try
    End Function

    ''' <summary>
    ''' Get Search String of the set object use to searching
    ''' </summary>
    ''' <param name="oCollection">Collection preferably the form where the object is place</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetSearchString(ByVal oCollection As Object) As String
        Dim obj As Object
        Dim tmpResult As String = ""
        Try
            For Each obj In oCollection.Controls
                If Mid$(obj.tag, 3, 1) = "!" Then
                    If TypeName(obj) = "TextBoxX" Then
                        tmpResult = tmpResult & IIf(tmpResult = "", obj.name & "='" & obj.Text & "'", _
                                                    " and " & obj.name & "='" & obj.Text & "'")
                    ElseIf TypeName(obj) = "DateTimeInput" Then
                        tmpResult = tmpResult & IIf(tmpResult = "", obj.name & "='" & obj.Text & "'", _
                                                    " and " & obj.name & "='" & obj.Text & "'")
                    ElseIf TypeName(obj) = "CheckBoxX" Then
                        tmpResult = tmpResult & IIf(tmpResult = "", obj.name & "='" & obj.Text & "'", _
                                                    " and " & obj.name & "='" & obj.Text & "'")
                    ElseIf TypeName(obj) = "ComboBoxEX" Then
                        tmpResult = tmpResult & IIf(tmpResult = "", obj.name & "='" & obj.Text & "'", _
                                                    " and " & obj.name & "='" & obj.Text & "'")
                    ElseIf IsContainer(obj) Then
                        tmpResult = tmpResult & GetSearchString(obj)
                    End If
                End If
            Next
            GetSearchString = tmpResult
        Catch ex As Exception
            GetSearchString = ""
        End Try
    End Function

    ''' <summary>
    ''' Determine if a control is a container or hold another control 
    ''' </summary>
    ''' <param name="obj">Object variable</param>
    ''' <returns>Boolean</returns>
    ''' <remarks></remarks>
    Public Function IsContainer(ByVal obj As Object) As Boolean
        If obj.controls.count > 0 Then
            Return True
        End If
    End Function

#End Region

End Class

